import pymysql

class DAO_exam_area:
    def __init__(self):
        pass

    def add(self, exa_year, exa_maj_name, exa_college_name, exa_uni_name, exa_subject1, exa_subject2, exa_subject3, exa_subject4):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "INSERT INTO sch_exam_area VALUES (null, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',null,null,null,null)" % (exa_year, exa_maj_name, exa_college_name, exa_uni_name, exa_subject1, exa_subject2, exa_subject3, exa_subject4)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print("insert ok")
        except Exception as e:
            # 发生错误时回滚
            print(e)
            print("(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 与数据库建立连接
    def get_conn(self):

        # 里皮的阿里云服务器
        # conn = pymysql.connect(host='39.105.39.215', port=3307, user="root", passwd="qq1040256886", db="yanxin", charset="utf8")

        # 本机的数据库
        # conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="root", db="yanxin", charset="utf8")
        conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="123", db="kaoyanpy", charset="utf8")
        return conn

    # 查询并输出所有数据
    def sel_all(self):
        cursor = self.get_conn().cursor()
        sql = 'select * from sch_exam_area'
        rows = cursor.execute(sql)
        res = cursor.fetchall()
        print('共有', rows, '条数据')
        for re in res:
            print(re)
        print('共有', rows, '条数据')

    # 查询表是否为空，为空返回true，不为空返回false，
    # 用于truncate()
    def is_empty(self):
        cursor = self.get_conn().cursor()
        sql = 'select * from sch_exam_area'
        rows = cursor.execute(sql)
        if rows > 0:
            return False
        else:
            return True

    # 查询学校 exam_area 是否已经爬取，已经爬取返回false，没爬取返回true
    def not_uni_name(self, exa_uni_name):
        cursor = self.get_conn().cursor()
        sql = 'select * from sch_exam_area where exa_uni_name = "%s"' % exa_uni_name
        rows = cursor.execute(sql)
        if rows > 0:
            return False
        else:
            return True

    # 清空recruit_page表
    def truncate(self):
        if self.is_empty():
            print("数据为空")
        else:
            sql = "truncate sch_exam_area"
            conn = self.get_conn()
            conn.cursor().execute(sql)
            conn.commit()
            print("已清空数据")
